﻿using GalaSoft.MvvmLight;
using GalaSoft.MvvmLight.Command;
using Newtonsoft.Json;
using OfficeHelper.Help;
using OfficeHelper.Model;
using OfficeHelper.Model.SQLGenerate;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Windows.Input;

namespace OfficeHelper.ViewModel
{
    public class SQLGenerateViewModel : OfficeHelpViewModelBase
    {

        public SQLGenerateViewModel()
        {
            _sqlFields = new ObservableCollection<SQLGenerateField>();
            FieldAddCommand = new RelayCommand(FieldAddMethod);
            FieldInsertCommand = new RelayCommand(FieldInsertMethod, CanSelect);
            FieldDeleteCommand = new RelayCommand(FieldDeleteMethod, CanSelect);
            FieldUpCommand = new RelayCommand(FieldUpMethod, CanSelect);
            FieldDownCommand = new RelayCommand(FieldDownMethod, CanSelect);
            AutoNamedCommand = new RelayCommand<bool>(AutoNamedMethod);
            ExcelInputCommand = new RelayCommand(ExcelInputMethod);
            ExcelSaveCommand = new RelayCommand(ExcelSaveMethod);
            ExcelOpenCommand = new RelayCommand(ExcelOpenMethod);
            ExcelInputMethod();//读取模版
            SQlGenerateCommand = new RelayCommand(SQlGenerateMethod);
            ReportGenerateCommand = new RelayCommand(ReportGenerateMethod);
            FieldLoadCommand = new RelayCommand<string>(FieldLoadMethod);
        }


        #region 报表文件生成

        public ICommand ReportGenerateCommand { get; set; }

        /// <summary>
        /// 报表文件生成
        /// </summary>
        private void ReportGenerateMethod()
        {
            View.CodeGenerate.ReportGenerateConfigView dialog = new View.CodeGenerate.ReportGenerateConfigView();
            dialog.ShowDialog();
        }


        public ICommand FieldLoadCommand { get; set; }

        /// <summary>
        /// 字段加载
        /// </summary>
        private void FieldLoadMethod(string type)
        {

            const string stringType = "varchar";
            const string stringTime = "datetime";
            const string stringDate = "date";
            const string stringInt = "int";
            int index = 1;
            SqlFields = new ObservableCollection<SQLGenerateField>();
            SQLGenerateField id = new SQLGenerateField()
            {
                IsKey = true,
                Index = index++,
                EnableNull = false,
                Length = "48",
                Name="ID",
                Remark = "ID",
                Type= stringType
            };

            SQLGenerateField creatorID = new SQLGenerateField()
            {
                IsKey = false,
                Index = index++,
                EnableNull = true,
                Length = "48",
                Name = "CreatorID",
                Remark = "创建者ID",
                Type = stringType
            };

            SQLGenerateField CreateTime = new SQLGenerateField()
            {
                IsKey = false,
                Index = index++,
                EnableNull = true,
                Name = "CreateTime",
                Remark = "创建时间",
                Type = stringTime,
                DefaultValue = "getDate()"
            };

            SQLGenerateField UpdateTime = new SQLGenerateField()
            {
                IsKey = false,
                Index = index++,
                EnableNull = true,
                Name = "UpdateTime",
                Remark = "更新时间",
                Type = stringTime
            };

            SQLGenerateField SiteDeptID = new SQLGenerateField()
            {
                IsKey = false,
                Index = index++,
                EnableNull = false,
                Name = "SiteDeptID",
                Remark = "矿ID",
                Length = "48",
                Type = stringType,
            };

            SQLGenerateField Sort = new SQLGenerateField()
            {
                IsKey = false,
                Index = index++,
                EnableNull = true,
                Name = "Sort",
                Remark = "排序",
                Type = stringInt,
            };

            

            SqlFields.Add(id);
            SqlFields.Add(creatorID);
            SqlFields.Add(CreateTime);
            SqlFields.Add(UpdateTime);
            SqlFields.Add(SiteDeptID);
            SqlFields.Add(Sort);
            if (type=="调度日报")
            {
                SQLGenerateField QueryDate = new SQLGenerateField()
                {
                    IsKey = false,
                    Index = index++,
                    EnableNull = false,
                    Name = "QueryDate",
                    Remark = "查询日期",
                    Type = stringDate,
                };
                SqlFields.Add(QueryDate);
            }

            if (type == "调度月报")
            {
                SQLGenerateField MonthDate = new SQLGenerateField()
                {
                    IsKey = false,
                    Index = index++,
                    EnableNull = false,
                    Name = "MonthDate",
                    Length = "7",
                    Remark = "月份 如:2019-08",
                    Type = stringType,
                };
                SqlFields.Add(MonthDate);
            }

            if (type == "调度年报")
            {
                SQLGenerateField YearDate = new SQLGenerateField()
                {
                    IsKey = false,
                    Index = index++,
                    EnableNull = false,
                    Name = "YearDate",
                    Remark = "年份 如:2019",
                    Type = stringInt,
                };
                SqlFields.Add(YearDate);
            }
        }


        #endregion



        #region 字段

        private SQLGenerateField _SelectField;

        /// <summary>
        /// 选中的字段
        /// </summary>
        public SQLGenerateField SelectField
        {
            get { return _SelectField; }
            set
            {
                Set("SelectField", ref _SelectField, value);
            }
        }

        private ObservableCollection<SQLGenerateField> _sqlFields;

        /// <summary>
        /// 选中的模版
        /// </summary>
        public ObservableCollection<SQLGenerateField> SqlFields
        {
            get { return _sqlFields; }
            set
            {
                Set("SqlFields", ref _sqlFields, value);
            }
        }


        private bool CanSelect()
        {
            if (SelectField != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新索引
        /// </summary>
        private void UpdateIndex()
        {
            int index = 1;
            foreach (var item in SqlFields)
            {
                item.Index = index++;
            }
        }

        public ICommand FieldInsertCommand { get; set; }


        private string _TableName;

        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName
        {
            get { return _TableName; }
            set
            {
                Set("TableName", ref _TableName, value);
            }
        }

        #endregion

        #region 命令
        /// <summary>
        /// 字段插入
        /// </summary>
        private void FieldInsertMethod()
        {
            SQLGenerateField field = GetField();
            int index = SqlFields.IndexOf(SelectField);
            SqlFields.Insert(index + 1, field);
            UpdateIndex();
        }

        private SQLGenerateField GetField()
        {
            if (SelectField == null)
            {
                SQLGenerateField nullField = new SQLGenerateField()
                {
                    IsKey = false,
                    EnableNull = true,
                    Type = "varchar2",
                    Length = "50",
                };
                return nullField;
            }
            else
            {
                var field = SelectField;
                SQLGenerateField newField = new SQLGenerateField()
                {
                    //IsKey = field.IsKey,
                    EnableNull = field.EnableNull,
                    Length = field.Length,
                    Type = field.Type
                };
                return newField;
            }
        }

        public ICommand FieldAddCommand { get; set; }

        /// <summary>
        /// 字段新增
        /// </summary>
        private void FieldAddMethod()
        {
            SqlFields.Add(GetField());
            UpdateIndex();
        }

        public ICommand FieldDeleteCommand { get; set; }

        /// <summary>
        /// 字段删除
        /// </summary>
        private void FieldDeleteMethod()
        {
            SqlFields.Remove(SelectField);
            UpdateIndex();
        }

        public ICommand FieldUpCommand { get; set; }

        /// <summary>
        /// 字段上移
        /// </summary>
        private void FieldUpMethod()
        {
            try
            {
                int index = SqlFields.IndexOf(SelectField);
                if (index > 0 && index < SqlFields.Count)
                {
                    var tmp = SelectField;
                    SqlFields.Remove(SelectField);
                    SqlFields.Insert(index - 1, tmp);
                    SelectField = tmp;
                    UpdateIndex();
                }
            }
            catch (Exception ex)
            {
                this.ShowMessageBox(ex.ToString());
            }
        }

        public ICommand FieldDownCommand { get; set; }

        /// <summary>
        /// 字段下移
        /// </summary>
        private void FieldDownMethod()
        {
            int index = SqlFields.IndexOf(SelectField);
            if (index >= 0 && index < SqlFields.Count - 1)
            {
                var tmp = SelectField;
                SqlFields.Remove(SelectField);
                SqlFields.Insert(index + 1, tmp);
                UpdateIndex();
                SelectField = tmp;
            }
        }


        public ICommand ExcelOpenCommand { get; set; }

        /// <summary>
        /// 打开Excel
        /// </summary>
        private void ExcelOpenMethod()
        {
            System.Diagnostics.Process.Start(Path.GetDirectoryName(ExcelPath));
        }

        public ICommand ExcelInputCommand { get; set; }

        /// <summary>
        /// Excel导入功能
        /// </summary>
        private void ExcelInputMethod()
        {
            ReadExcel();
        }


        public ICommand ExcelSaveCommand { get; set; }

        /// <summary>
        /// Excel  保存
        /// </summary>
        private void ExcelSaveMethod()
        {
            try
            {
                FileInfo fileInfo = new FileInfo(ExcelPath);

                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    //ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("SqlTable");//创建worksheet
                    worksheet.Cells["A1"].Value = "表名";
                    worksheet.Cells["B1"].Value = TableName;

                    worksheet.Cells["A2"].Value = "序号";
                    worksheet.Cells["B2"].Value = "名称";
                    worksheet.Cells["C2"].Value = "类型";
                    worksheet.Cells["D2"].Value = "长度";
                    worksheet.Cells["E2"].Value = "备注";
                    worksheet.Cells["F2"].Value = "可空";
                    worksheet.Cells["G2"].Value = "主键";

                    int index = 3;
                    foreach (var item in SqlFields)
                    {
                        worksheet.Cells[index,1].Value = item.Index;
                        worksheet.Cells[index, 2].Value = item.Name;
                        worksheet.Cells[index, 3].Value = item.Type;
                        worksheet.Cells[index, 4].Value = item.Length;
                        worksheet.Cells[index, 5].Value = item.Remark;
                        worksheet.Cells[index, 6].Value = item.EnableNull?"1":"";
                        worksheet.Cells[index, 7].Value = item.IsKey?"1":"";
                        worksheet.Cells[index, 8].Value = item.DefaultValue;
                        index++;
                    }
                    package.Save();//保存excel
                };
            }
            catch (Exception ex)
            {

                ShowMessageBox(ex.ToString());
            }

        }

        public ICommand SQlGenerateCommand { get; set; }

        /// <summary>
        /// 生成SQl
        /// </summary>
        private void SQlGenerateMethod()
        {
            string sql = SQLServerSQLHelp.CreateTable(SqlFields,TableName);

            View.Editer.EditerView.Show(sql,"TSQL");
            //ShowMessageBox(sql);
        }

        #endregion

        /// <summary>
        /// Excel文件的路径
        /// </summary>
        private string ExcelPath = Common.CommonDir.SQLGenerateFullExcelPath;
        
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        private void ReadExcel()
        {
            try
            {
                //如果文件存在
                if (File.Exists(ExcelPath))
                {
                    using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelPath)))
                    {

                        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                        //获取表格的列数和行数
                        int rowCount = worksheet.Dimension.Rows;
                        int ColCount = worksheet.Dimension.Columns;

                        if (rowCount < 3 || ColCount <6)
                        {
                            ShowMessageBox(string.Format("表格列数{0},行数{1}不符合!",ColCount,rowCount));
                            return;
                        }

                        TableName = worksheet.GetCellsValueString("B1");

                        ObservableCollection<SQLGenerateField> sqlFields = new ObservableCollection<SQLGenerateField>();
                        for (int row = 3; row <= rowCount; row++)
                        {
                            SQLGenerateField field = new SQLGenerateField()
                            {
                                Index = worksheet.GetCellsValueInt(row, 1)??0,
                                Name = worksheet.GetCellsValueString(row, 2),
                                Type = worksheet.GetCellsValueString(row, 3),
                                Length = worksheet.GetCellsValueString(row, 4),
                                Remark = worksheet.GetCellsValueString(row, 5),
                                EnableNull = worksheet.GetCellsValueString(row, 6).Trim() == "1" ? true : false,
                                IsKey = worksheet.GetCellsValueString(row, 7).Trim() == "1" ? true : false,
                                DefaultValue = worksheet.GetCellsValueString(row, 8)
                            };
                            sqlFields.Add(field);
                        }
                        SqlFields = sqlFields;
                    }
                }
            }
            catch (Exception ex)
            {
                ShowMessageBox(ex.ToString());
                throw;
            }
        }

        public ICommand AutoNamedCommand { get; set; }

        /// <summary>
        /// 自动命名功能
        /// </summary>
        private void AutoNamedMethod(bool isFlip)
        {
            foreach (var item in SqlFields)
            {
                //名称到备注
                if (isFlip)
                {
                    if (string.IsNullOrEmpty(item.Remark))
                    {
                        //备注不为空
                        if (!string.IsNullOrEmpty(item.Name))
                        {
                            var strRemak = Translate(item.Name, "auto", "zh");
                            if (!string.IsNullOrEmpty(strRemak))
                            {
                                item.Remark = strRemak.Trim();
                            }
                        }
                    }
                }
                else //备注->名称
                {
                    //名称为空进行翻译
                    if (string.IsNullOrEmpty(item.Name))
                    {
                        //备注不为空
                        if (!string.IsNullOrEmpty(item.Remark))
                        {
                            var strName = Translate(item.Remark, "auto", "en");
                            if (!string.IsNullOrEmpty(strName))
                            {
                                item.Name = strName.Trim();
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 翻译命令
        /// </summary>
        /// <param name="TranSrc">需要翻译的字符串</param>
        /// <param name="from">原始语言</param>
        /// <param name="to">要翻译到的语音</param>
        /// <returns>结果</returns>
        private string Translate(string TranSrc, string from, string to)
        {
            try
            {
                if (String.IsNullOrEmpty(MySetting.BaiduTranKey) || MySetting.BaiduTranAppid == 0)
                {
                    return TranslateMyApi(TranSrc,from,to);
                }
                else
                {
                    return TranslateBaidu(TranSrc, from, to);
                }
            }
            catch (Exception ex)
            {
                ShowMessageBox(ex.ToString());
            }
            return null;
        }

        private string TranslateMyApi(string TranSrc, string from, string to)
        {
            WebClient client = new WebClient();
            TranSrc = TranSrc.Replace(@"#", "%23");
            TranslateBaiduRequestObj translateBaiduRequestObj = new TranslateBaiduRequestObj();
            translateBaiduRequestObj.q = TranSrc;
            translateBaiduRequestObj.from = from;
            translateBaiduRequestObj.to = to;
            translateBaiduRequestObj.appid = SettingsModel.Default.BaiduTranAppid;
            translateBaiduRequestObj.key = SettingsModel.Default.BaiduTranKey;
            string url = translateBaiduRequestObj.GetRequestStrByChenChaoAPI();
            var buffer = client.DownloadData(url);
            string result = Encoding.UTF8.GetString(buffer);

            StringReader sr = new StringReader(result);
            JsonTextReader jsonReader = new JsonTextReader(sr);
            JsonSerializer serializer = new JsonSerializer();
            var r = serializer.Deserialize<TranslateMyAPITObj>(jsonReader);
            if (r.success)
            {
                String dst = "";
                foreach (var item in r.result.trans_result)
                {
                    dst += item.dst + Environment.NewLine;
                }
                return dst;
            }
            return null;

        }

        /// <summary>
        /// 百度翻译
        /// </summary>
        private string TranslateBaidu(string TranSrc, string from, string to)
        {
            WebClient client = new WebClient();
            TranSrc = TranSrc.Replace(@"#", "%23");
            TranslateBaiduRequestObj translateBaiduRequestObj = new TranslateBaiduRequestObj();
            translateBaiduRequestObj.q = TranSrc;
            translateBaiduRequestObj.from = from;
            translateBaiduRequestObj.to = to;
            translateBaiduRequestObj.appid = SettingsModel.Default.BaiduTranAppid;
            translateBaiduRequestObj.key = SettingsModel.Default.BaiduTranKey;
            string url = translateBaiduRequestObj.GetRequestStr();
            var buffer = client.DownloadData(url);
            string result = Encoding.UTF8.GetString(buffer);

            StringReader sr = new StringReader(result);
            JsonTextReader jsonReader = new JsonTextReader(sr);
            JsonSerializer serializer = new JsonSerializer();
            var r = serializer.Deserialize<TranslateBaiduResultObj>(jsonReader);
            String dst = "";
            foreach (var item in r.trans_result)
            {
                dst += item.dst;
            }
            return dst;
        }
    }
}
